Imports System.Collections
Imports System.Text.RegularExpressions
Imports System.Data.OracleClient

Public Class clsGenClas

  Dim clsobjFileGen As New clsGnrtFile

  Dim lStrCode As String = ""
  Private mStrScriptCode As String = ""

  Dim colTableCoulmns As ArrayList

  Dim mCmdData As OracleCommand
  Dim mDrData As OracleDataReader
  Dim mDrStringIndc As OracleDataReader
  Dim lBlRecExist As Boolean = False
  Private mStrTableName As String = ""
  Private mIntTabelId As Integer

#Region " CLASS MEMBER PROPERTY "
  Public Property mPrpTableId() As Integer
    Get
      Return mIntTabelId
    End Get
    Set(ByVal value As Integer)
      mIntTabelId = value
    End Set
  End Property

#End Region

  Public Function mFnGenerateClass(ByVal aStrTableName As String _
                                 , ByVal aStrMainClassName As String _
                                 , ByVal aIntTableId As Integer) As Boolean


    If mFnCheckTableValidity(aStrTableName) = False Then
      MessageBox.Show("Table doesnot exist", gCONST_MSG_HEADER)
      Exit Function
    End If
    mStrTableName = aStrTableName
    mIntTabelId = aIntTableId

    clsobjFileGen = New clsGnrtFile()
    lStrCode = ""
    'lStrCode = lStrCode + "Imports System.Data.OracleClient" + vbCrLf
    lStrCode = lStrCode + "Imports System.Data.Common" + vbCrLf
    lStrCode = lStrCode + "Imports com.TCS.NGCNYTS.CM.DataTypeLib.ApplicationTypes" + vbCrLf

    'Nithya check 
    '    lStrCode = lStrCode + "Namespace DataAccessLayer" + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES2
    lStrCode = lStrCode + "Public Class clsTbl" & Trim(aStrMainClassName)
    lStrCode = lStrCode + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES4
    lStrCode = lStrCode + "Inherits clsDataAccess" + vbCrLf + vbCrLf

    colTableCoulmns = Nothing

    GenerateMemVars()
    GenerateDBRegion()
    GenerateConstructor()
    GenerateInitializeData()
    GenerateMemberFunctions()
    GenerateMemberProperties()

    '    lStrCode = lStrCode + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES2
    lStrCode = lStrCode + "End Class" + vbCrLf
    'Nithya check 
    'lStrCode = lStrCode + "End Namespace"

    clsobjFileGen.mprGnrtVbFiles(lStrCode, "clsTbl" & aStrMainClassName, "Table Classes")
    Return True

  End Function

  Private Function mFnCheckTableValidity(ByVal aStrTableName As String) As Boolean

    Dim lStrQuery As String = ""
    Dim lIntCount As Integer

    lStrQuery = "select count(*) as CNT from TRDX_TABLES_MSTR where TABM_TABLE_NAME = UPPER('"
    lStrQuery = lStrQuery + aStrTableName + "')"

    mCmdData = New OracleCommand(lStrQuery, con)
    lIntCount = mCmdData.ExecuteScalar

    If lIntCount = 0 Then
      Return False
    Else
      Return True
    End If

  End Function

  Private Sub GenerateENUM()

    lStrCode = lStrCode + "#Region " + Chr(34) + " CLASS ENUM DECLARATION " + Chr(34) + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES2
    lStrCode = lStrCode + "Public Enum enmQueryIndicator"
    lStrCode = lStrCode + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES4
    lStrCode = lStrCode + "ALL" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES4
    lStrCode = lStrCode + "PRIMARY_KEY" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES2
    lStrCode = lStrCode + "End Enum" + vbCrLf + vbCrLf
    lStrCode = lStrCode + "#End Region" + vbCrLf + vbCrLf


  End Sub

  Private Sub GenerateMemVars()

    lStrCode = lStrCode + "#Region " + Chr(34) + " CLASS MEMBER VARIABLES " + Chr(34) + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES2
    lStrCode = lStrCode + "Private Const CONST_ZERO As Integer = 0"
    lStrCode = lStrCode + vbCrLf + vbCrLf
    lStrCode = lStrCode + "#End Region" + vbCrLf + vbCrLf

  End Sub

  Private Sub GenerateDBRegion()

    lStrCode = lStrCode + "#Region " + Chr(34) + " CLASS DATABASE COLUMNS " + Chr(34) + vbCrLf + vbCrLf
    GetCols()
    While (mDrData.Read)

      lStrCode = lStrCode + gCONST_SPACES2
      'lStrCode = lStrCode + "Private mObj" + mDrData("TAB_COL_DATA_TYPE") + " As " + mDrData("TAB_COL_NAME") + vbCrLf
      lStrCode = lStrCode + "Private mObj" + mDrData("TAB_COL_NAME") + " As " + mDrData("TAB_COL_DATA_TYPE") + vbCrLf
    End While


    lStrCode = lStrCode + vbCrLf + "#End Region" + vbCrLf + vbCrLf

  End Sub

  Private Sub GetCols()

    Dim lStrQuery As String = ""

    'lStrQuery = "select distinct TAB_COL_NAME,TAB_COL_DATA_TYPE,TAB_COL_NULL_INDC,TAB_COL_SEQ_NO"
    'lStrQuery = lStrQuery + ", TAB_COL_BASIC_DATA_TYPE, TAB_COL_DATA_LENGTH , TAB_COL_DATA_PRECISION from "
    'lStrQuery = lStrQuery + "TRDX_TABLE_COLUMNS where TAB_TABLE_ID = ("
    'lStrQuery = lStrQuery + "select distinct TABM_TABLE_ID from TRDX_TABLES_MSTR where "
    'lStrQuery = lStrQuery + "TABM_TABLE_NAME = upper('"
    'lStrQuery = lStrQuery + mStrTableName + "')) order by TAB_COL_SEQ_NO ASC"

    lStrQuery = "select distinct TAB_COL_NAME,TAB_COL_DATA_TYPE,TAB_COL_NULL_INDC,TAB_COL_SEQ_NO"
    lStrQuery = lStrQuery + ", TAB_COL_BASIC_DATA_TYPE, TAB_COL_DATA_LENGTH , TAB_COL_DATA_PRECISION from "
    lStrQuery = lStrQuery + "TRDX_TABLE_COLUMNS where TAB_TABLE_ID = " & mIntTabelId
    lStrQuery = lStrQuery + " order by TAB_COL_SEQ_NO ASC"

    mCmdData = New OracleCommand(lStrQuery, con)
    mDrData = mCmdData.ExecuteReader

  End Sub

  Private Sub GetPkConstraints()

    Dim lStrQuery As String = ""
    Dim lIntRecCount As Integer = 0


    'lStrQuery = "select count(*) from TRDX_TABL_INDX where "
    'lStrQuery = lStrQuery + "TTL_TABL_IDXX  = (select TABM_TABLE_ID "
    'lStrQuery = lStrQuery + "from TRDX_TABLES_MSTR where TABM_TABLE_NAME = upper('"
    'lStrQuery = lStrQuery + mStrTableName + "')) and TTL_INDX_TYPE = 'P' "

    lStrQuery = "select count(*) from TRDX_TABL_INDX where "
    lStrQuery = lStrQuery + "TTL_TABL_IDXX  = " & mIntTabelId
    lStrQuery = lStrQuery + " and TTL_INDX_TYPE = 'P' "

    mCmdData = New OracleCommand(lStrQuery, con)
    lIntRecCount = mCmdData.ExecuteScalar

    If lIntRecCount = 0 Then
      lBlRecExist = False
      Exit Sub
    Else
      lBlRecExist = True
    End If

    'lStrQuery = "select distinct TAB_COL_SEQ_NO ,TAB_COL_NAME,TAB_COL_DATA_TYPE,TAB_COL_BASIC_DATA_TYPE from "
    'lStrQuery = lStrQuery + "TRDX_TABLE_COLUMNS where TAB_COL_ID in ("
    'lStrQuery = lStrQuery + "select TTL_COLM_IDXX from TRDX_TABL_INDX where "
    'lStrQuery = lStrQuery + "TTL_TABL_IDXX  = (select TABM_TABLE_ID "
    'lStrQuery = lStrQuery + "from TRDX_TABLES_MSTR where TABM_TABLE_NAME = upper('"
    'lStrQuery = lStrQuery + mStrTableName + "')) and TTL_INDX_TYPE = 'P') and TAB_TABLE_ID = (select TABM_TABLE_ID "
    'lStrQuery = lStrQuery + "from TRDX_TABLES_MSTR where TABM_TABLE_NAME = upper('"
    'lStrQuery = lStrQuery + mStrTableName + "'))"
    'lStrQuery = lStrQuery + " order by TAB_COL_SEQ_NO"

    lStrQuery = "select distinct TAB_COL_SEQ_NO ,TAB_COL_NAME,TAB_COL_DATA_TYPE,TAB_COL_BASIC_DATA_TYPE from "
    lStrQuery = lStrQuery + "TRDX_TABLE_COLUMNS where TAB_COL_ID in ("
    lStrQuery = lStrQuery + "select TTL_COLM_IDXX from TRDX_TABL_INDX where "
    lStrQuery = lStrQuery + "TTL_TABL_IDXX  = " & mIntTabelId
    lStrQuery = lStrQuery + " and TTL_INDX_TYPE = 'P' and TAB_TABLE_ID = " & mIntTabelId & ")"
    lStrQuery = lStrQuery + " order by TAB_COL_SEQ_NO"

    mCmdData = New OracleCommand(lStrQuery, con)
    mDrData = mCmdData.ExecuteReader


  End Sub

  Private Sub GetUkConstraints()

    Dim lStrQuery As String = ""
    Dim lIntRecCount As Integer = 0


    'lStrQuery = "select count(*) from TRDX_TABL_INDX where "
    'lStrQuery = lStrQuery + "TTL_TABL_IDXX  = (select TABM_TABLE_ID "
    'lStrQuery = lStrQuery + "from TRDX_TABLES_MSTR where TABM_TABLE_NAME = upper('"
    'lStrQuery = lStrQuery + mStrTableName + "')) and TTL_INDX_TYPE = 'I' "

    lStrQuery = "select count(*) from TRDX_TABL_INDX where "
    lStrQuery = lStrQuery + "TTL_TABL_IDXX  = " & mIntTabelId
    lStrQuery = lStrQuery + " and TTL_INDX_TYPE = 'I' "

    mCmdData = New OracleCommand(lStrQuery, con)
    lIntRecCount = mCmdData.ExecuteScalar

    If lIntRecCount = 0 Then
      lBlRecExist = False
      Exit Sub
    Else
      lBlRecExist = True
    End If

    'lStrQuery = "select distinct TAB_COL_SEQ_NO ,TAB_COL_NAME,TAB_COL_DATA_TYPE,TAB_COL_BASIC_DATA_TYPE from "
    'lStrQuery = lStrQuery + "TRDX_TABLE_COLUMNS where TAB_COL_ID in ("
    'lStrQuery = lStrQuery + "select TTL_COLM_IDXX from TRDX_TABL_INDX where "
    'lStrQuery = lStrQuery + "TTL_TABL_IDXX  = (select TABM_TABLE_ID "
    'lStrQuery = lStrQuery + "from TRDX_TABLES_MSTR where TABM_TABLE_NAME = upper('"
    'lStrQuery = lStrQuery + mStrTableName + "')) and TTL_INDX_TYPE = 'I') and TAB_TABLE_ID = (select TABM_TABLE_ID "
    'lStrQuery = lStrQuery + "from TRDX_TABLES_MSTR where TABM_TABLE_NAME = upper('"
    'lStrQuery = lStrQuery + mStrTableName + "'))"
    'lStrQuery = lStrQuery + " order by TAB_COL_SEQ_NO"

    lStrQuery = "select distinct TAB_COL_SEQ_NO ,TAB_COL_NAME,TAB_COL_DATA_TYPE,TAB_COL_BASIC_DATA_TYPE from "
    lStrQuery = lStrQuery + "TRDX_TABLE_COLUMNS where TAB_COL_ID in ("
    lStrQuery = lStrQuery + "select TTL_COLM_IDXX from TRDX_TABL_INDX where "
    lStrQuery = lStrQuery + "TTL_TABL_IDXX  = " & mIntTabelId
    lStrQuery = lStrQuery + " and TTL_INDX_TYPE = 'I' and TAB_TABLE_ID = " & mIntTabelId & ")"
    lStrQuery = lStrQuery + " order by TAB_COL_SEQ_NO"

    mCmdData = New OracleCommand(lStrQuery, con)
    mDrData = mCmdData.ExecuteReader


  End Sub

  Private Function fnVarPrfix(ByVal aDataType As String, ByVal aReturnType As Boolean)

    Dim lPassResult As String = ""

    'aReturnType is TRUE for prefix for a variable and FALSE for a datatype after AS

    Select Case UCase((aDataType))

      Case ("NUMBER")
        If aReturnType = True Then
          lPassResult = "Int"
        Else
          lPassResult = "Integer"
        End If
      Case ("VARCHA")
        If aReturnType = True Then
          lPassResult = "Str"
        Else
          lPassResult = "String"
        End If
      Case ("CHAR")
        If aReturnType = True Then
          lPassResult = "Str"
        Else
          lPassResult = "String"
        End If
      Case ("DATE")
        If aReturnType = True Then
          lPassResult = "Dtm"
        Else
          lPassResult = "DateTime"
        End If
      Case Else
        lPassResult = ""
    End Select

    Return lPassResult

  End Function

  Private Sub GenerateConstructor()

    Dim lBlIndcFlag As Boolean = True
    lStrCode = lStrCode + "#Region " + Chr(34) + " CLASS CONSTRUCTOR " + Chr(34) + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES2
    lStrCode = lStrCode + "Public Sub New()" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES4
    lStrCode = lStrCode + "mPrpConnectionDB = clsConnectionManager.enmConnectionDB.SECURED_DB"
    lStrCode = lStrCode + UCase(Trim(frmGnrtScript.mPrGetInstance.cboDataBase.Text)) + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES4
    lStrCode = lStrCode + "mPrInitializeData()" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES2 + "End Sub" + vbCrLf + vbCrLf

  End Sub

  Private Sub GenerateMemberFunctions()

    lStrCode = lStrCode + "# Region " + Chr(34) + " CLASS MEMBER FUNCTIONS " + Chr(34) + vbCrLf + vbCrLf

    GenerateSelect()
    GenerateInsert()
    GenerateUpdate()
    GenerateDelete()
    GenerateDeleteAll()
    GenerateRetrieveAll()
    GeneratePopulateAll()
    GenerateClearDbEntities()

    lStrCode = lStrCode + vbCrLf
    lStrCode = lStrCode + "#End Region" + vbCrLf + vbCrLf

  End Sub

  Private Sub GenerateSelect()

    Dim lBlIndcFlag As Boolean = True
    Dim lBlFirstEntry As Boolean = True
    Dim lBlnStringIndc As Boolean = False

    '***************************************************************************************************
    'Generate select query

    GetCols()

    lStrCode = lStrCode + "#Region " + Chr(34) + " SELECT " + Chr(34) + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES2
    lStrCode = lStrCode + "Private Sub mPrSelect" + frmGnrtScript.mPrGetInstance.txtMainClassName.Text
    'lStrCode = lStrCode + "(ByRef aBlnDataExists As Boolean)" + vbCrLf + vbCrLf
    'lStrCode = lStrCode + gCONST_SPACES4
    'lStrCode = lStrCode + "Dim lObjDataReader As DbDataReader" + vbCrLf
    lStrCode = lStrCode + "(ByRef aObjDataReader As DbDataReader)" + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES4
    lStrCode = lStrCode + "Dim lStrSQLString As String" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES4
    lStrCode = lStrCode + "lStrSQLString = " + " _" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES6
    lStrCode = lStrCode + Chr(34) + "Select" + Chr(34) + " & _" + vbCrLf
    While mDrData.Read

      If lBlFirstEntry = True Then
        lBlFirstEntry = False

        lStrCode = lStrCode + gCONST_SPACES6
        lStrCode = lStrCode + Chr(34) + gCONST_SPACES1 + mDrData("TAB_COL_NAME")
        lStrCode = lStrCode + gCONST_SPACES1 + Chr(34) + " & _" + vbCrLf
      Else
        lStrCode = lStrCode + gCONST_SPACES6
        lStrCode = lStrCode + Chr(34) + "," + gCONST_SPACES1
        lStrCode = lStrCode + mDrData("TAB_COL_NAME") + Chr(34) + " & _" + vbCrLf
      End If

    End While

    lStrCode = lStrCode + gCONST_SPACES6
    lStrCode = lStrCode + Chr(34) + gCONST_SPACES1 + "From "
    lStrCode = lStrCode + UCase(Trim(mstrTableName))

    GetPkConstraints()

    If lBlRecExist = True Then

      lStrCode = lStrCode + Chr(34) + " & _" + vbCrLf
      lStrCode = lStrCode + gCONST_SPACES6
      lStrCode = lStrCode + Chr(34) + " Where "

      While mDrData.Read
        If lBlIndcFlag = False Then       ' first entry in the where clause so no and
          lStrCode = lStrCode + vbCrLf
          lStrCode = lStrCode + gCONST_SPACES6
          lStrCode = lStrCode + Chr(34)
          lStrCode = lStrCode + gCONST_SPACES3 + "And "
        End If

        lBlIndcFlag = False
        If mDrData("TAB_COL_BASIC_DATA_TYPE") = "Y" Then
          lBlnStringIndc = True
        Else
          lBlnStringIndc = False
        End If

        lStrCode = lStrCode + UCase(Trim(mDrData("TAB_COL_NAME"))) '+ " = "
        If lBlnStringIndc = True Then
          lStrCode = lStrCode + " = '"
        Else
          lStrCode = lStrCode + " = "
        End If
        lStrCode = lStrCode + Chr(34)
        lStrCode = lStrCode + " & mObj"
        lStrCode = lStrCode + mDrData("TAB_COL_NAME") + ".mPrpValue"
        If lBlnStringIndc = True Then
          lStrCode = lStrCode + " & " + Chr(34) + "'" + Chr(34)
        End If

        lStrCode = lStrCode + " & _"

      End While

      lStrCode = lStrCode.Remove(lStrCode.Length - 4, 4)
      lStrCode = lStrCode + vbCrLf + vbCrLf + vbCrLf
    Else
      lStrCode = lStrCode + Chr(34) + vbCrLf + vbCrLf
    End If
    'Select query completed
    '***************************************************************************************************

    lStrCode = lStrCode + gCONST_SPACES4
    lStrCode = lStrCode + "aObjDataReader = mFnExecuteReader(lStrSQLString)" + vbCrLf + vbCrLf
    'lStrCode = lStrCode + gCONST_SPACES4
    'lStrCode = lStrCode + "If lObjDataReader.HasRows Then" + vbCrLf
    'lStrCode = lStrCode + gCONST_SPACES6
    'lStrCode = lStrCode + "lObjDataReader.Read()" + vbCrLf
    'lStrCode = lStrCode + gCONST_SPACES6
    'lStrCode = lStrCode + "aBlnDataExists = True" + vbCrLf
    'lStrCode = lStrCode + gCONST_SPACES6
    'lStrCode = lStrCode + "mPrPopulateData _" + vbCrLf
    'lStrCode = lStrCode + gCONST_SPACES8
    'lStrCode = lStrCode + "(lObjDataReader _" + vbCrLf
    'lStrCode = lStrCode + gCONST_SPACES6 + gCONST_SPACES1
    'lStrCode = lStrCode + ", enmQueryIndicator.ALL)" + vbCrLf
    'lStrCode = lStrCode + gCONST_SPACES4
    'lStrCode = lStrCode + "Else" + vbCrLf
    'lStrCode = lStrCode + gCONST_SPACES6
    'lStrCode = lStrCode + "aBlnDataExists = False" + vbCrLf
    'lStrCode = lStrCode + gCONST_SPACES4
    'lStrCode = lStrCode + "End If" + vbCrLf + vbCrLf
    'lStrCode = lStrCode + gCONST_SPACES4
    'lStrCode = lStrCode + "lObjDataReader.Close()" + vbCrLf + vbCrLf

    lStrCode = lStrCode + gCONST_SPACES2
    lStrCode = lStrCode + "End Sub" + vbCrLf
    lStrCode = lStrCode + vbCrLf
    lStrCode = lStrCode + "#End Region" + vbCrLf + vbCrLf

  End Sub

  Private Sub GenerateInsert()

    Dim lBlIndcFlag As Boolean = True
    Dim lBlLastFlag As Boolean = False
    Dim lBlFirstEntry As Boolean = True
    Dim lStrPrfix As String = ""
    Dim lStrLastPrfix As String = ""
    Dim lBlnStringIndc As Boolean = False

    lStrCode = lStrCode + "#Region " + Chr(34) + " INSERT " + Chr(34) + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES2
    lStrCode = lStrCode + "Public Function mFnInsert" + frmGnrtScript.mPrGetInstance.txtMainClassName.Text
    lStrCode = lStrCode + "() As Integer" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES4
    lStrCode = lStrCode + "Dim lStrSQLString As String" + vbCrLf + vbCrLf

    '**************************************************************************************************
    'Insert Query Begins

    GetCols()

    lStrCode = lStrCode + gCONST_SPACES4
    lStrCode = lStrCode + "lStrSQLString = " + "_" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES6
    lStrCode = lStrCode + Chr(34) + "Insert Into " + UCase(Trim(mstrTableName))
    lStrCode = lStrCode + " (" + Chr(34) + " & _" + vbCrLf

    While mDrData.Read
      If lBlFirstEntry = True Then
        lBlFirstEntry = False

        lStrCode = lStrCode + gCONST_SPACES6
        lStrCode = lStrCode + Chr(34) + gCONST_SPACES1 + mDrData("TAB_COL_NAME")
        lStrCode = lStrCode + gCONST_SPACES1 + Chr(34) + " & _" + vbCrLf
      Else
        lStrCode = lStrCode + gCONST_SPACES6
        lStrCode = lStrCode + Chr(34) + "," + gCONST_SPACES1
        lStrCode = lStrCode + mDrData("TAB_COL_NAME") + Chr(34) + " & _" + vbCrLf
      End If
    End While

    lStrCode = lStrCode.Remove(lStrCode.Length - 7, 7)

    lStrCode = lStrCode + " ) " + Chr(34) + " & _"
    lStrCode = lStrCode + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES6
    lStrCode = lStrCode + Chr(34) + " Values " + Chr(34) + " & _" + vbCrLf

    GetCols()

    While mDrData.Read
      If mDrData("TAB_COL_BASIC_DATA_TYPE") = "Y" Then
        lBlnStringIndc = True
      Else
        lBlnStringIndc = False
      End If

      If lBlIndcFlag = True Then
        lBlIndcFlag = False
        lStrCode = lStrCode + gCONST_SPACES6
        If lBlnStringIndc = True Then
          lStrCode = lStrCode + Chr(34) + "('" + Chr(34)
        Else
          lStrCode = lStrCode + Chr(34) + "( " + Chr(34)
        End If

        lStrCode = lStrCode + " & mObj" + mDrData("TAB_COL_NAME") + ".mPrpValue" '+ " & _"

        If lBlnStringIndc = True Then
          lStrCode = lStrCode + " & " + Chr(34) + "'" + Chr(34)
        End If

        lStrCode = lStrCode + " & _"
      Else
        lStrCode = lStrCode + vbCrLf
        lStrCode = lStrCode + gCONST_SPACES6
        If lBlnStringIndc = True Then
          lStrCode = lStrCode + Chr(34) + ",'" + Chr(34)
        Else
          lStrCode = lStrCode + Chr(34) + ", " + Chr(34)

        End If

        lStrCode = lStrCode + " & mObj" + mDrData("TAB_COL_NAME") + ".mPrpValue" '+ " & _"
        If lBlnStringIndc = True Then
          lStrCode = lStrCode + " & " + Chr(34) + "'" + Chr(34)
        End If

        lStrCode = lStrCode + " & _"

        lBlLastFlag = False
      End If

    End While

    'Select query completed
    '***************************************************************************************************

    lStrCode = lStrCode.Remove(lStrCode.Length - 4, 4)
    lStrCode = lStrCode + " & " + Chr(34)

    If lBlLastFlag = True Then
      lStrCode = lStrCode + "')"
    Else
      lStrCode = lStrCode + ")"
    End If

    lStrCode = lStrCode + Chr(34)

    lStrCode = lStrCode + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES4
    lStrCode = lStrCode + "Return mFnExecuteNonQuery(lStrSQLString)" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES2
    lStrCode = lStrCode + "End Function" + vbCrLf + vbCrLf
    lStrCode = lStrCode + "#End Region" + vbCrLf + vbCrLf

  End Sub

  Private Sub GenerateUpdate()

    Dim lStrPrfix As String = ""
    Dim lBlFirstEntry As Boolean = True
    Dim lBlnStringIndc As Boolean = False

    lStrCode = lStrCode + "#Region " + Chr(34) + " UPDATE " + Chr(34) + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES2
    lStrCode = lStrCode + "Public Function mFnUpdate"
    lStrCode = lStrCode + Trim(frmGnrtScript.mPrGetInstance.txtMainClassName.Text)
    lStrCode = lStrCode + "() As Integer" + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES4
    lStrCode = lStrCode + "Dim lStrSQLString As String" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES4
    lStrCode = lStrCode + "lStrSQLString = " + " _" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES6
    lStrCode = lStrCode + Chr(34) + "Update " + UCase(Trim(mstrTableName))
    lStrCode = lStrCode + gCONST_SPACES1
    lStrCode = lStrCode + "Set " + Chr(34) + " & _"

    '*********************************************************************************************************
    'Update query begins

    GetCols()

    While mDrData.Read

      lStrCode = lStrCode + vbCrLf

      If mDrData("TAB_COL_BASIC_DATA_TYPE") = "Y" Then
        lBlnStringIndc = True
      Else
        lBlnStringIndc = False
      End If

      If lBlFirstEntry = True Then
        lBlFirstEntry = False

        lStrCode = lStrCode + gCONST_SPACES6
        lStrCode = lStrCode + Chr(34) + gCONST_SPACES1 + mDrData("TAB_COL_NAME") '+ " = "
        If lBlnStringIndc = True Then
          lStrCode = lStrCode + " = '"
        Else
          lStrCode = lStrCode + " = "
        End If

      Else
        lStrCode = lStrCode + gCONST_SPACES6
        lStrCode = lStrCode + Chr(34) + "," + gCONST_SPACES1
        lStrCode = lStrCode + mDrData("TAB_COL_NAME") '+ " = "
        If lBlnStringIndc = True Then
          lStrCode = lStrCode + " = '"
        Else
          lStrCode = lStrCode + " = "
        End If

      End If

      lStrCode = lStrCode + Chr(34)
      lStrCode = lStrCode + " & mObj" + mDrData("TAB_COL_NAME") + ".mPrpValue"

      If lBlnStringIndc = True Then
        lStrCode = lStrCode + " & " + Chr(34) + "'" + Chr(34)
      End If

      lStrCode = lStrCode + " & _"

    End While

    GetPkConstraints()
    If lBlRecExist = True Then
      lBlFirstEntry = True

      While mDrData.Read

        lStrCode = lStrCode + vbCrLf
        lStrCode = lStrCode + gCONST_SPACES6 + Chr(34)

        If lBlFirstEntry = True Then
          lBlFirstEntry = False
          lStrCode = lStrCode + " Where "
        Else
          lStrCode = lStrCode + gCONST_SPACES2 + " And "
        End If

        lStrCode = lStrCode + mDrData("TAB_COL_NAME")
        lStrCode = lStrCode + " = "

        lStrCode = lStrCode + Chr(34)

        lStrCode = lStrCode + gCONST_SPACES1 + "& mObj" + mDrData("TAB_COL_NAME") + ".mPrpValue"

        lStrCode = lStrCode + " & _"

      End While
    End If
    'Update query completed
    '*********************************************************************************************************


    lStrCode = lStrCode.Remove(lStrCode.Length - 4, 4)

    lStrCode = lStrCode + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES4
    lStrCode = lStrCode + "Return mFnExecuteNonQuery(lStrSQLString)" + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES2
    lStrCode = lStrCode + "End Function" + vbCrLf
    lStrCode = lStrCode + "#End Region" + vbCrLf + vbCrLf

  End Sub

  Private Sub GenerateDelete()

    Dim lStrPrfix As String = ""
    Dim lBlFirstRec As Boolean = True
    Dim lBlnStringIndc As Boolean = False

    lStrCode = lStrCode + "#Region " + Chr(34) + " DELETE " + Chr(34) + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES2
    lStrCode = lStrCode + "Public Function mFnDelete"
    lStrCode = lStrCode + Trim(frmGnrtScript.mPrGetInstance.txtMainClassName.Text)
    lStrCode = lStrCode + "() As Integer" + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES4
    lStrCode = lStrCode + "Dim lStrSQLString As String" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES4
    lStrCode = lStrCode + "lStrSQLString = " + " _ " + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES6
    lStrCode = lStrCode + Chr(34) + "Delete From " + UCase(Trim(mstrTableName))
    lStrCode = lStrCode + gCONST_SPACES1 + Chr(34) + " & _"

    '*********************************************************************************************************
    'Delete query begins


    GetPkConstraints()
    If lBlRecExist = True Then
      lBlFirstRec = True

      While mDrData.Read

        lStrCode = lStrCode + vbCrLf
        lStrCode = lStrCode + gCONST_SPACES6 + Chr(34)

        If lBlFirstRec = True Then
          lBlFirstRec = False
          lStrCode = lStrCode + "Where "
        Else
          lStrCode = lStrCode + gCONST_SPACES2 + "And "
        End If

        lStrCode = lStrCode + mDrData("TAB_COL_NAME")
        If mDrData("TAB_COL_BASIC_DATA_TYPE") = "Y" Then
          lBlnStringIndc = True
        Else
          lBlnStringIndc = False
        End If

        If lBlnStringIndc = True Then

          lStrCode = lStrCode + " = '"

        Else
          lStrCode = lStrCode + " = "

        End If

        lStrCode = lStrCode + Chr(34)

        lStrCode = lStrCode + gCONST_SPACES1 + "& mObj" + mDrData("TAB_COL_NAME") + ".mPrpValue"

        If lBlnStringIndc = True Then
          lStrCode = lStrCode + " & " + Chr(34) + "'" + Chr(34)
        End If

        lStrCode = lStrCode + " & _"

      End While
    End If
    'Update query completed
    '*********************************************************************************************************


    lStrCode = lStrCode.Remove(lStrCode.Length - 4, 4)

    lStrCode = lStrCode + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES4
    lStrCode = lStrCode + "Return mFnExecuteNonQuery(lStrSQLString)" + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES2
    lStrCode = lStrCode + "End Function" + vbCrLf
    lStrCode = lStrCode + "#End Region" + vbCrLf + vbCrLf

  End Sub

  Private Sub GenerateDeleteAll()

    lStrCode = lStrCode + "#Region " + Chr(34) + " DELETE ALL " + Chr(34) + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES2
    lStrCode = lStrCode + "Public Function mFnDeleteAll"
    lStrCode = lStrCode + Trim(frmGnrtScript.mPrGetInstance.txtMainClassName.Text)
    lStrCode = lStrCode + "() As Integer" + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES4
    lStrCode = lStrCode + "Dim lStrSQLString As String" + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES4
    lStrCode = lStrCode + "lStrSQLString = " + Chr(34) + "delete from " + UCase(Trim(mstrTableName))
    lStrCode = lStrCode + gCONST_SPACES1 + Chr(34)
    lStrCode = lStrCode + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES4
    lStrCode = lStrCode + "Return mFnExecuteNonQuery(lStrSQLString)" + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES2
    lStrCode = lStrCode + "End Function" + vbCrLf
    lStrCode = lStrCode + "#End Region" + vbCrLf + vbCrLf

  End Sub

  Private Sub GenerateRetrieve()

    lStrCode = lStrCode + "#Region " + Chr(34) + " RETRIEVE " + Chr(34) + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES2
    lStrCode = lStrCode + "Public Sub mPrRetrieveData _" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES8 + gCONST_SPACES8 + gCONST_SPACES2
    lStrCode = lStrCode + "(ByRef aObjDataReader as DbDataReader _" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES8 + gCONST_SPACES8 + gCONST_SPACES1
    lStrCode = lStrCode + ", ByVal aenmQueryIndicator As enmQueryIndicator)" + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES4
    lStrCode = lStrCode + "Select Case (aenmQueryIndicator)" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES6
    lStrCode = lStrCode + "Case enmQueryIndicator.ALL" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES8
    lStrCode = lStrCode + "mPrRetrieveAll(aObjDataReader)" + vbCrLf + vbCrLf
    'lStrCode = lStrCode + gCONST_SPACES8
    'lStrCode = lStrCode + "Exit Select" + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES6
    lStrCode = lStrCode + "Case enmQueryIndicator.PRIMARY_KEY" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES8
    lStrCode = lStrCode + "mPrSelect" + frmGnrtScript.mPrGetInstance.txtMainClassName.Text + "(aObjDataReader)" + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES6
    lStrCode = lStrCode + "Case Else" + vbCrLf
    'lStrCode = lStrCode + gCONST_SPACES8
    'lStrCode = lStrCode + "Exit Select" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES4
    lStrCode = lStrCode + "End Select" + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES2
    lStrCode = lStrCode + "End Sub" + vbCrLf + vbCrLf
    lStrCode = lStrCode + "#End Region" + vbCrLf + vbCrLf

  End Sub

  Private Sub GenerateRetrieveAll()
    Dim lBlFirstEntry As Boolean = True

    lStrCode = lStrCode + "#Region " + Chr(34) + " RETRIEVE ALL " + Chr(34) + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES2
    'lStrCode = lStrCode + "Private Sub mprRetrieveAll(ByRef aObjDataReader As OracleDataReader)" + vbCrLf + vbCrLf
    lStrCode = lStrCode + "Private Sub mPrRetrieveAll(ByRef aObjDataReader As DbDataReader)" + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES4
    lStrCode = lStrCode + "Dim lStrSQLString As String" + vbCrLf + vbCrLf

    GetCols()

    lStrCode = lStrCode + gCONST_SPACES4
    lStrCode = lStrCode + "lStrSQLString = " + " _" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES6
    lStrCode = lStrCode + Chr(34) + "Select " + Chr(34) + " & _" + vbCrLf

    While mDrData.Read

      If lBlFirstEntry = True Then
        lBlFirstEntry = False

        lStrCode = lStrCode + gCONST_SPACES6
        lStrCode = lStrCode + Chr(34) + gCONST_SPACES1 + mDrData("TAB_COL_NAME")
        lStrCode = lStrCode + Chr(34) + " & _" + vbCrLf
      Else
        lStrCode = lStrCode + gCONST_SPACES6
        lStrCode = lStrCode + Chr(34) + "," + gCONST_SPACES1
        lStrCode = lStrCode + mDrData("TAB_COL_NAME") + Chr(34) + " & _" + vbCrLf
      End If
    End While

    lStrCode = lStrCode + gCONST_SPACES6
    lStrCode = lStrCode + Chr(34) + gCONST_SPACES1 + "From "
    lStrCode = lStrCode + UCase(Trim(mStrTableName))
    lStrCode = lStrCode + Chr(34) + vbCrLf + vbCrLf

    lStrCode = lStrCode + gCONST_SPACES4
    lStrCode = lStrCode + "aObjDataReader = mFnExecuteReader(lStrSQLString)" + vbCrLf + vbCrLf
    lStrCode = lStrCode + "End Sub" + vbCrLf + vbCrLf
    lStrCode = lStrCode + "#End Region" + vbCrLf + vbCrLf

  End Sub

  Private Sub GenerateRetrieveNextRecord()

    'Populate region starts here

    lStrCode = lStrCode + "#Region " + Chr(34) + " RETRIEVE NEXT RECORD " + Chr(34) + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES2
    lStrCode = lStrCode + "Public Function mFnRetrieveNextRecord _" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES8 + gCONST_SPACES8 + gCONST_SPACES2
    'lStrCode = lStrCode + "(ByRef aObjDataReader as OracleDataReader _" + vbCrLf
    lStrCode = lStrCode + "(ByRef aObjDataReader as DbDataReader _" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES8 + gCONST_SPACES8 + gCONST_SPACES1
    lStrCode = lStrCode + ", ByVal aenmQueryIndicator As enmQueryIndicator) As Boolean" + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES4
    lStrCode = lStrCode + "Dim lBlnRecordExists As Boolean = True" + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES4
    lStrCode = lStrCode + "If Not aObjDataReader.Read = True Then" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES6
    lStrCode = lStrCode + "lBlnRecordExists = False" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES6
    lStrCode = lStrCode + "aObjDataReader.Close()" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES4
    lStrCode = lStrCode + "Else" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES6
    lStrCode = lStrCode + "mPrPopulateData _" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES6 + gCONST_SPACES6 + gCONST_SPACES1
    lStrCode = lStrCode + "(aObjDataReader _" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES6 + gCONST_SPACES6
    lStrCode = lStrCode + ", aenmQueryIndicator)" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES4
    lStrCode = lStrCode + "End If" + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES4
    lStrCode = lStrCode + "Return lBlnRecordExists" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES2
    lStrCode = lStrCode + "End Function" + vbCrLf + vbCrLf
    lStrCode = lStrCode + "#End Region" + vbCrLf + vbCrLf


  End Sub

  Private Sub GeneratePopulateData()

    lStrCode = lStrCode + "#Region " + Chr(34) + " POPULATE DATA " + Chr(34) + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES2
    lStrCode = lStrCode + "Private Sub mPrPopulateData _" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES8 + gCONST_SPACES8 + gCONST_SPACES2
    'lStrCode = lStrCode + "(ByRef aObjDataReader As OracleDataReader _" + vbCrLf
    lStrCode = lStrCode + "(ByRef aObjDataReader As DbDataReader _" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES8 + gCONST_SPACES8 + gCONST_SPACES1
    lStrCode = lStrCode + ", ByVal aenmQueryIndicator As enmQueryIndicator)" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES4
    lStrCode = lStrCode + "Select Case (aenmQueryIndicator)" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES6
    lStrCode = lStrCode + "Case enmQueryIndicator.ALL" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES8
    lStrCode = lStrCode + "mPrPopulateAll(aObjDataReader)" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES6
    lStrCode = lStrCode + "Case enmQueryIndicator.PRIMARY_KEY" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES8
    lStrCode = lStrCode + "mPrPopulateAll(aObjDataReader)" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES6
    lStrCode = lStrCode + "Case Else" + vbCrLf
    'lStrCode = lStrCode + gCONST_SPACES8
    'lStrCode = lStrCode + "Exit Select" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES4
    lStrCode = lStrCode + "End Select" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES2
    lStrCode = lStrCode + "End Sub" + vbCrLf + vbCrLf
    lStrCode = lStrCode + "#End Region" + vbCrLf + vbCrLf

  End Sub

  Private Sub GeneratePopulateAll()

    Dim lStrPrfx As String = ""

    lStrCode = lStrCode + "#Region " + Chr(34) + " POPULATE ALL " + Chr(34) + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES2
    'lStrCode = lStrCode + "Private Sub mprPopulateAll(ByRef aObjDataReader As OracleDataReader)" + vbCrLf
    lStrCode = lStrCode + "Private Sub mPrPopulateAll(ByRef aObjDataReader As DbDataReader)" + vbCrLf
    lStrCode = lStrCode + vbCrLf

    'GetCols()

    'While mDrData.Read

    '  lStrPrfx = fnVarPrfix(UCase(Trim(Mid(mDrData("TAB_COL_DATA_TYPE"), 1, 6))), True)
    '  lStrCode = lStrCode + gCONST_SPACES4
    '  lStrCode = lStrCode + "m" + lStrPrfx + UCase(Trim(mDrData("TAB_COL_NAME"))) + " = "
    '  If Len("m" + lStrPrfx + UCase(Trim(mDrData("TAB_COL_NAME")))) > 24 Then
    '    lStrCode = lStrCode + " _" + vbCrLf
    '    lStrCode = lStrCode + gCONST_SPACES8 + gCONST_SPACES2
    '  End If
    '  lStrCode = lStrCode + "aObjDataReader.Item(" + Chr(34) + UCase(Trim(mDrData("TAB_COL_NAME")))
    '  lStrCode = lStrCode + Chr(34) + ")" + vbCrLf

    'End While

    'lStrCode = lStrCode + vbCrLf + vbCrLf
    GetCols()

    While mDrData.Read
      'If mDrData("TAB_COL_NULL_INDC") = "N" Then  'Column is not nullable

      lStrPrfx = fnVarPrfix(UCase(Trim(Mid(mDrData("TAB_COL_DATA_TYPE"), 1, 6))), True)
      lStrCode = lStrCode + gCONST_SPACES4

      'If lStrPrfx <> "Str" Then
      '  lStrCode = lStrCode + "If IsNumeric(aObjDataReader.Item(" + Chr(34)
      'Else
      '  lStrCode = lStrCode + "If Not IsDBNull(aObjDataReader.Item(" + Chr(34)
      'End If

      'lStrCode = lStrCode + mDrData("TAB_COL_NAME") + Chr(34) + "))" + vbCrLf
      'lStrCode = lStrCode + gCONST_SPACES6
      'lStrCode = lStrCode + "m" + lStrPrfx + mDrData("TAB_COL_NAME") + " = "

      'If Len("m" + lStrPrfx + mDrData("TAB_COL_NAME") + " = ") > 26 Then
      '  lStrCode = lStrCode + " _"
      '  lStrCode = lStrCode + vbCrLf
      '  lStrCode = lStrCode + gCONST_SPACES8 + gCONST_SPACES2
      'End If

      'If lStrPrfx <> "Str" Then
      '  lStrCode = lStrCode + "If IsNumeric(aObjDataReader.Item(" + Chr(34)
      'Else
      lStrCode = lStrCode + "If Not IsDBNull(aObjDataReader.Item(" + Chr(34)
      '     End If

      lStrCode = lStrCode + mDrData("TAB_COL_NAME") + Chr(34) + "))" + vbCrLf
      lStrCode = lStrCode + gCONST_SPACES6
      lStrCode = lStrCode + "mObj" + mDrData("TAB_COL_NAME") + ".mPrpValue" + " = "

      If Len("mObj" + mDrData("TAB_COL_NAME") + ".mPrpValue" + " = ") > 26 Then
        lStrCode = lStrCode + " _"
        lStrCode = lStrCode + vbCrLf
        lStrCode = lStrCode + gCONST_SPACES8 + gCONST_SPACES2
      End If

      lStrCode = lStrCode + "aObjDataReader.Item(" + Chr(34) + mDrData("TAB_COL_NAME")
      lStrCode = lStrCode + Chr(34) + ")" + vbCrLf
      lStrCode = lStrCode + gCONST_SPACES4
      lStrCode = lStrCode + "End If" + vbCrLf + vbCrLf
      'End If

    End While

    lStrCode = lStrCode + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES2
    lStrCode = lStrCode + "End Sub" + vbCrLf + vbCrLf

    'Populate region ends here

    lStrCode = lStrCode + "#End Region" + vbCrLf + vbCrLf


  End Sub

  Private Sub GenerateClearDbEntities()

    Dim lStrPrfx As String = ""

    lStrCode = lStrCode + "#Region " + Chr(34) + " CLEAR DB ENTITIES " + Chr(34) + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES2
    lStrCode = lStrCode + "Public Sub mPrClearDBEntities()" + vbCrLf

    GetCols()

    While mDrData.Read

      ' lStrPrfx = fnVarPrfix(UCase(Trim(Mid(mDrData("TAB_COL_DATA_TYPE"), 1, 6))), True)
      lStrCode = lStrCode + gCONST_SPACES4
      'lStrCode = lStrCode + "m" + lStrPrfx + mDrData("TAB_COL_NAME") + " = "
      lStrCode = lStrCode + "mObj" + mDrData("TAB_COL_NAME") + ".mPrpValue" + " = "

      lStrCode = lStrCode + Chr(34) + Chr(34)

      lStrCode = lStrCode + vbCrLf

    End While

    lStrCode = lStrCode + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES2
    lStrCode = lStrCode + "End Sub" + vbCrLf + vbCrLf

    lStrCode = lStrCode + "#End Region" + vbCrLf + vbCrLf

  End Sub

  Private Sub GenerateMemberProperties()

    Dim lStrPrefix As String = ""

    lStrCode = lStrCode + "#Region " + Chr(34) + " CLASS PROPERTIES " + Chr(34) + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES2 + vbCrLf

    GetCols()

    While mDrData.Read
      lStrPrefix = mDrData("TAB_COL_NAME") 'Mid(mDrData("TAB_COL_NAME"), 3)
      lStrCode = lStrCode + "Public Property mPrp" + lStrPrefix + "()" + " As " + _
                 mDrData("TAB_COL_DATA_TYPE") + vbCrLf

      lStrCode = lStrCode + gCONST_SPACES2 + "Get" + vbCrLf
      lStrCode = lStrCode + gCONST_SPACES4 + "Return " + "mObj" + mDrData("TAB_COL_NAME") + vbCrLf
      lStrCode = lStrCode + gCONST_SPACES2 + "End Get" + vbCrLf
      lStrCode = lStrCode + gCONST_SPACES2 + "Set (ByVal value as " + mDrData("TAB_COL_DATA_TYPE") + ")" + vbCrLf
      lStrCode = lStrCode + gCONST_SPACES4 + "mObj" + mDrData("TAB_COL_NAME") + " = value" + vbCrLf
      lStrCode = lStrCode + gCONST_SPACES2 + "End Set" + vbCrLf
      lStrCode = lStrCode + "End Property" + vbCrLf + vbCrLf

    End While

    lStrCode = lStrCode + "#End Region" + vbCrLf + vbCrLf

  End Sub

  Private Sub GenerateHelperMemberFunctions()

    lStrCode = lStrCode + "# Region " + Chr(34) + " CLASS MEMBER FUNCTIONS " + Chr(34) + vbCrLf + vbCrLf

    GenerateRetrieve()
    GenerateRetrieveNextRecord()
    GeneratePopulateData()

    ' lStrCode = lStrCode + vbCrLf
    lStrCode = lStrCode + "#End Region" + vbCrLf + vbCrLf

  End Sub

  Public Function mFnGenerateHelperClass(ByVal aStrTableName As String _
                                        , ByVal aStrMainClassName As String) As Boolean


    If mFnCheckTableValidity(aStrTableName) = False Then
      MessageBox.Show("Table doesnot exist", gCONST_MSG_HEADER)
      Exit Function
    End If

    clsobjFileGen = New clsGnrtFile()
    lStrCode = ""
    lStrCode = lStrCode + "Imports System.Data.Common" + vbCrLf
    lStrCode = lStrCode + "Imports com.TCS.NGCNYTS.CM.DataTypeLib.ApplicationTypes" + vbCrLf

    lStrCode = lStrCode + gCONST_SPACES2
    lStrCode = lStrCode + "Partial Class clsTbl" & Trim(aStrMainClassName)
    lStrCode = lStrCode + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES4
    lStrCode = lStrCode + "Inherits clsDataAccess" + vbCrLf + vbCrLf

    colTableCoulmns = Nothing

    GenerateENUM()
    GenerateHelperRegions()
    GenerateHelperMemberFunctions()

    'lStrCode = lStrCode + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES2
    lStrCode = lStrCode + "End Class" + vbCrLf

    clsobjFileGen.mprGnrtHelperFile(lStrCode, "clsTbl" & aStrMainClassName, "Table Classes")
    Return True

  End Function

  Private Sub GenerateInitializeData()

    Dim lBlIndcFlag As Boolean = True
    'lStrCode = lStrCode + "#Region " + Chr(34) + " CLASS CONSTRUCTOR " + Chr(34) + vbCrLf + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES2
    lStrCode = lStrCode + "Private Sub mPrInitializeData()" + vbCrLf
    lStrCode = lStrCode + gCONST_SPACES4
    GetCols()
    While (mDrData.Read)

      If lBlIndcFlag = True Then
        '        lStrCode = lStrCode + gCONST_SPACES2
        lBlIndcFlag = False
      Else
        lStrCode = lStrCode + gCONST_SPACES4
      End If

      lStrCode = lStrCode + "mObj" + mDrData("TAB_COL_NAME") + " = New " + mDrData("TAB_COL_DATA_TYPE") + vbCrLf

    End While
    lStrCode = lStrCode + gCONST_SPACES2 + "End Sub" + vbCrLf + vbCrLf
    lStrCode = lStrCode + "#End Region" + vbCrLf + vbCrLf

  End Sub

  Private Sub GenerateHelperRegions()
    lStrCode = lStrCode + "#Region " + Chr(34) + " CLASS CUSTOMIZED QUERIES " + Chr(34) + vbCrLf + vbCrLf
    lStrCode = lStrCode + "#Region " + Chr(34) + " SELECT " + Chr(34) + vbCrLf + vbCrLf
    lStrCode = lStrCode + "#End Region" + vbCrLf + vbCrLf
    lStrCode = lStrCode + "#Region " + Chr(34) + " INSERT " + Chr(34) + vbCrLf + vbCrLf
    lStrCode = lStrCode + "#End Region" + vbCrLf + vbCrLf
    lStrCode = lStrCode + "#Region " + Chr(34) + " UPDATE " + Chr(34) + vbCrLf + vbCrLf
    lStrCode = lStrCode + "#End Region" + vbCrLf + vbCrLf
    lStrCode = lStrCode + "#Region " + Chr(34) + " DELETE " + Chr(34) + vbCrLf + vbCrLf
    lStrCode = lStrCode + "#End Region" + vbCrLf + vbCrLf
    lStrCode = lStrCode + "#End Region" + vbCrLf + vbCrLf
  End Sub

  Private Sub GetDataCtgry(ByVal aStrClmnDataType As String)

    Dim lStrQuery As String = ""
    Dim lStrClmnDataType As String = ""

    lStrClmnDataType = aStrClmnDataType.Substring(3)
    lStrQuery = "select distinct TDCM_DATA_CTGRY From "
    lStrQuery = lStrQuery + "TRDX_DATA_CATEGORY_MSTR where "
    lStrQuery = lStrQuery + "TDCM_DESC = '"
    lStrQuery = lStrQuery + lStrClmnDataType + "'"
 
    mCmdData = New OracleCommand(lStrQuery, con)
    mDrStringIndc = mCmdData.ExecuteReader

  End Sub

  Private Function mfnGetTableShortName(ByVal aStrTableName As String) As String

    Dim lStrQuery As String = ""

    lStrQuery = "select distinct TABM_TABLE_SHORT_NAME from TRDX_TABLES_MSTR where "
    lStrQuery = lStrQuery + " TABM_TABLE_NAME = upper('" + aStrTableName + "')  "

    mCmdData = New OracleCommand(lStrQuery, con)
    mfnGetTableShortName = mCmdData.ExecuteScalar

  End Function

  Public Function mFnGenerateSQLCEScript(ByVal aStrTableName As String _
                                        , ByVal aStrFileName As String) As Boolean

    mPrGnrtSQLCEScript(aStrTableName, aStrFileName)

    Return True

  End Function

  Private Sub mPrGnrtSQLCEScript(ByVal aStrTableName As String _
                               , ByVal aStrFileName As String)


    Dim lStrDataType As String = ""
    Dim lStrTableShrtName As String
    Dim lStrNullIndc As String = ""

    lStrTableShrtName = mfnGetTableShortName(aStrTableName)

    GetCols()

    mStrScriptCode = ""

    mStrScriptCode = mStrScriptCode & "CREATE TABLE [" & UCase(aStrTableName) & "]"

    mStrScriptCode = mStrScriptCode & vbCrLf

    mStrScriptCode = mStrScriptCode & gCONST_SPACES4

    mStrScriptCode = mStrScriptCode & "("

    While mDrData.Read
      lStrDataType = ""
      mStrScriptCode = mStrScriptCode & vbCrLf
      mStrScriptCode = mStrScriptCode & gCONST_SPACES4
      mStrScriptCode = mStrScriptCode & "[" & mDrData("TAB_COL_NAME") & "]"
      lStrDataType = mFnRtrnBasicDataType(mDrData("TAB_COL_DATA_TYPE").ToString, True)
      mStrScriptCode = mStrScriptCode & gCONST_SPACES1 & lStrDataType
      If lStrDataType = "NVARCHAR" Then
        mStrScriptCode = mStrScriptCode & "(" & mDrData("TAB_COL_DATA_LENGTH").ToString & ")"
      End If
      lStrNullIndc = mDrData("TAB_COL_NULL_INDC")

      If lStrNullIndc = "Y" Then
        mStrScriptCode = mStrScriptCode & gCONST_SPACES1 & "NOT NULL" & ","
      ElseIf lStrNullIndc = "N" Then
        mStrScriptCode = mStrScriptCode & gCONST_SPACES1 & "NULL" & ","
      End If


    End While

    mStrScriptCode = mStrScriptCode.Substring(0, mStrScriptCode.Length - 1)

    mStrScriptCode = mStrScriptCode & vbCrLf

    mStrScriptCode = mStrScriptCode & ")"

    mStrScriptCode = mStrScriptCode & vbCrLf

    mStrScriptCode = mStrScriptCode & "GO"

    GetPkConstraints()

    If lBlRecExist = True Then
      
      mStrScriptCode = mStrScriptCode & vbCrLf

      mStrScriptCode = mStrScriptCode & vbCrLf

      mStrScriptCode = mStrScriptCode & "ALTER TABLE [" & UCase(aStrTableName) & "] "

      mStrScriptCode = mStrScriptCode & "ADD" & vbCrLf
      mStrScriptCode = mStrScriptCode & gCONST_SPACES4

      mStrScriptCode = mStrScriptCode & "CONSTRAINT [PK_" & UCase(lStrTableShrtName) & "] PRIMARY KEY ("

      While mDrData.Read

        mStrScriptCode = mStrScriptCode & vbCrLf
        mStrScriptCode = mStrScriptCode & gCONST_SPACES4 & gCONST_SPACES4
        mStrScriptCode = mStrScriptCode & "[" & mDrData("TAB_COL_NAME") & "],"

      End While

      mStrScriptCode = mStrScriptCode.Substring(0, mStrScriptCode.Length - 1)

      mStrScriptCode = mStrScriptCode & vbCrLf

      mStrScriptCode = mStrScriptCode & ")"

      mStrScriptCode = mStrScriptCode & vbCrLf

      mStrScriptCode = mStrScriptCode & "GO"

      mStrScriptCode = mStrScriptCode & vbCrLf

    End If

    GetUkConstraints()

    If lBlRecExist = True Then

      mStrScriptCode = mStrScriptCode & vbCrLf

      mStrScriptCode = mStrScriptCode & vbCrLf

      mStrScriptCode = mStrScriptCode & "ALTER TABLE [" & UCase(aStrTableName) & "] "

      mStrScriptCode = mStrScriptCode & "ADD" & vbCrLf
      mStrScriptCode = mStrScriptCode & gCONST_SPACES4

      mStrScriptCode = mStrScriptCode & "CONSTRAINT [UK_" & UCase(lStrTableShrtName) & "] UNIQUE ("

      While mDrData.Read

        mStrScriptCode = mStrScriptCode & vbCrLf
        mStrScriptCode = mStrScriptCode & gCONST_SPACES4 & gCONST_SPACES4
        mStrScriptCode = mStrScriptCode & "[" & mDrData("TAB_COL_NAME") & "],"

      End While

      mStrScriptCode = mStrScriptCode.Substring(0, mStrScriptCode.Length - 1)

      mStrScriptCode = mStrScriptCode & vbCrLf

      mStrScriptCode = mStrScriptCode & ")"

      mStrScriptCode = mStrScriptCode & vbCrLf

      mStrScriptCode = mStrScriptCode & "GO"

      mStrScriptCode = mStrScriptCode & vbCrLf

    End If

    clsobjFileGen.mPrGnrtDbScriptFile(mStrScriptCode, aStrFileName, "SQLCE Script")

  End Sub

  Private Function mFnRtrnBasicDataType(ByVal aStrDataType As String, ByVal aBlnClntSrvrIndc As Boolean) _
    As String

    Dim lStrDataType As String = ""
    Dim lStrQuery As String = ""
    Dim lIntTableId As Integer = 0
    Dim lStrFrontEndDataCatg As String = ""


    lStrFrontEndDataCatg = aStrDataType.Substring(3)
    If aBlnClntSrvrIndc = True Then

   
      lStrQuery = lStrQuery + "select distinct TDCM_FRNT_END_DATA_TYPE "
      lStrQuery = lStrQuery + "from TRDX_DATA_CATEGORY_MSTR where "
      lStrQuery = lStrQuery + "TDCM_FRNT_END_DATA_CATEGORY = '" + lStrFrontEndDataCatg + "'"

    Else

      lStrQuery = lStrQuery + "select distinct TDCM_DATABASE_DATA_TYPE "
      lStrQuery = lStrQuery + "from TRDX_DATA_CATEGORY_MSTR where "
      lStrQuery = lStrQuery + "TDCM_FRNT_END_DATA_CATEGORY = '" + lStrFrontEndDataCatg + "'"

    End If

    mCmdData = New OracleCommand(lStrQuery, con)
    lStrDataType = mCmdData.ExecuteScalar

    Return lStrDataType

  End Function

  Private Function mFnTableId() As Integer

    Dim lStrQuery As String = ""
    Dim lIntTableId As Integer = 0

    lStrQuery = lStrQuery + "select distinct TABM_TABLE_ID from TRDX_TABLES_MSTR where "
    lStrQuery = lStrQuery + "TABM_TABLE_NAME = upper('"
    lStrQuery = lStrQuery + mStrTableName + "'))"

    mCmdData = New OracleCommand(lStrQuery, con)
    lIntTableId = mCmdData.ExecuteScalar

    Return lIntTableId

  End Function

  Public Function mFnGnrtOracleScript(ByVal aStrTableName As String _
                                    , ByVal aStrFileName As String) As Boolean

    mPrGnrtOracleScript(aStrTableName, aStrFileName)

    Return True
  End Function

  Private Sub mPrGnrtOracleScript(ByVal aStrTableName As String _
                                , ByVal aStrFileName As String)

    Dim lStrDataType As String = ""
    Dim lStrTableShrtName As String
    Dim lIntDataLength As Integer
    Dim lIntDecimalLength As Integer

    lStrTableShrtName = mfnGetTableShortName(aStrTableName)
    GetCols()

    mStrScriptCode = ""
    'mStrScriptCode = mStrScriptCode & vbCrLf
    'mStrScriptCode = mStrScriptCode & vbCrLf

    mStrScriptCode = mStrScriptCode & "DROP TABLE " & UCase(aStrTableName) & " CASCADE CONSTRAINTS ;" + vbCrLf

    mStrScriptCode = mStrScriptCode & "CREATE TABLE " & UCase(aStrTableName)

    mStrScriptCode = mStrScriptCode & vbCrLf

    mStrScriptCode = mStrScriptCode & "("


    While mDrData.Read

      lStrDataType = ""
      mStrScriptCode = mStrScriptCode & vbCrLf
      mStrScriptCode = mStrScriptCode & gCONST_SPACES4
      mStrScriptCode = mStrScriptCode & mDrData("TAB_COL_NAME")
      lStrDataType = mFnRtrnBasicDataType(mDrData("TAB_COL_DATA_TYPE").ToString, False)
      mStrScriptCode = mStrScriptCode & gCONST_SPACES1 & lStrDataType
      If UCase(lStrDataType) = "VARCHAR2" Then

        If Not IsDBNull(mDrData("TAB_COL_DATA_LENGTH")).ToString = True Then
          mStrScriptCode = mStrScriptCode & "(" & mDrData("TAB_COL_DATA_LENGTH").ToString & ")"
        End If

      ElseIf UCase(lStrDataType) = "NUMBER" Or UCase(lStrDataType) = "RAW" Then

        If Not IsDBNull(mDrData("TAB_COL_DATA_LENGTH")) = True Then

          lIntDataLength = mDrData("TAB_COL_DATA_LENGTH")

          If (IsDBNull(mDrData("TAB_COL_DATA_PRECISION")) = True) _
                        And ((mDrData("TAB_COL_DATA_PRECISION")) = 0) Then
            lIntDecimalLength = 0
          Else
            lIntDecimalLength = mDrData("TAB_COL_DATA_PRECISION")

          End If

          mStrScriptCode = mStrScriptCode & "(" & (lIntDataLength + lIntDecimalLength)

          If (Not IsDBNull(mDrData("TAB_COL_DATA_PRECISION")) = True) _
              And ((mDrData("TAB_COL_DATA_PRECISION")) <> 0) Then

            mStrScriptCode = mStrScriptCode & "," & mDrData("TAB_COL_DATA_PRECISION") & ")"

          Else
            mStrScriptCode = mStrScriptCode & ")"

          End If

        End If

      End If

      If mDrData("TAB_COL_NULL_INDC").ToString = "Y" Then
        mStrScriptCode = mStrScriptCode & gCONST_SPACES1 & "NOT NULL" & ","
      Else
        mStrScriptCode = mStrScriptCode & gCONST_SPACES1 & ","
      End If

    End While

    mStrScriptCode = mStrScriptCode.Substring(0, mStrScriptCode.Length - 1)

    mStrScriptCode = mStrScriptCode & vbCrLf

    mStrScriptCode = mStrScriptCode & ")"

    mStrScriptCode = mStrScriptCode & vbCrLf

    mStrScriptCode = mStrScriptCode & "TABLESPACE USERS ;"

    ' ================= Primary Key =====================

    GetPKConstraints()

    If lBlRecExist = True Then
      mStrScriptCode = mStrScriptCode & vbCrLf

      mStrScriptCode = mStrScriptCode & vbCrLf

      mStrScriptCode = mStrScriptCode & "ALTER TABLE " & UCase(aStrTableName)

      mStrScriptCode = mStrScriptCode & " ADD" & vbCrLf
      mStrScriptCode = mStrScriptCode & gCONST_SPACES4

      mStrScriptCode = mStrScriptCode & "CONSTRAINT PK_" & UCase(lStrTableShrtName) & " PRIMARY KEY ("

      While mDrData.Read

        mStrScriptCode = mStrScriptCode & vbCrLf
        mStrScriptCode = mStrScriptCode & gCONST_SPACES4 & gCONST_SPACES4
        mStrScriptCode = mStrScriptCode & mDrData("TAB_COL_NAME") & ","

      End While

      mStrScriptCode = mStrScriptCode.Substring(0, mStrScriptCode.Length - 1)
      mStrScriptCode = mStrScriptCode & ")"

      mStrScriptCode = mStrScriptCode & vbCrLf

      mStrScriptCode = mStrScriptCode & "USING INDEX"

      mStrScriptCode = mStrScriptCode & vbCrLf

      mStrScriptCode = mStrScriptCode & "TABLESPACE INDX ;"


      mStrScriptCode = mStrScriptCode & vbCrLf

    End If
    ' =================Unique Key =====================

    GetUkConstraints()

    If lBlRecExist = True Then
      mStrScriptCode = mStrScriptCode & vbCrLf

      mStrScriptCode = mStrScriptCode & vbCrLf

      mStrScriptCode = mStrScriptCode & "ALTER TABLE " & UCase(aStrTableName)

      mStrScriptCode = mStrScriptCode & " ADD" & vbCrLf
      mStrScriptCode = mStrScriptCode & gCONST_SPACES4

      mStrScriptCode = mStrScriptCode & "CONSTRAINT UK_" & UCase(lStrTableShrtName) & " UNIQUE KEY ("

      While mDrData.Read

        mStrScriptCode = mStrScriptCode & vbCrLf
        mStrScriptCode = mStrScriptCode & gCONST_SPACES4 & gCONST_SPACES4
        mStrScriptCode = mStrScriptCode & mDrData("TAB_COL_NAME") & ","

      End While

      mStrScriptCode = mStrScriptCode.Substring(0, mStrScriptCode.Length - 1)
      mStrScriptCode = mStrScriptCode & ")"

      mStrScriptCode = mStrScriptCode & vbCrLf

      mStrScriptCode = mStrScriptCode & "USING INDEX"

      mStrScriptCode = mStrScriptCode & vbCrLf

      mStrScriptCode = mStrScriptCode & "TABLESPACE INDX ;"


      mStrScriptCode = mStrScriptCode & vbCrLf

    End If
    clsobjFileGen.mPrGnrtDbScriptFile(mStrScriptCode, aStrTableName, "ORACLE Script")

  End Sub

  Public Function mFnGnrtClassName(ByVal aStrClassName As String) As String

    Dim lChrArrString As String()
    Dim lStrClassName As String = ""
    Dim lStrCamelCasing As String = ""
    Dim lStrQuery As String = ""
    'Dim lDrDataReader As OracleDataReader

    lChrArrString = aStrClassName.Split("_")

    For lIntCnt As Integer = 0 To lChrArrString.Length - 1
      'lStrQuery = "Select initcap('" & lChrArrString(lIntCnt) & "') TableName from Dual"
      'mCmdData = New OracleCommand(lStrQuery, con)

      'lDrDataReader = mCmdData.ExecuteReader

      lStrCamelCasing = StrConv(lChrArrString(lIntCnt), VbStrConv.ProperCase)

      lStrClassName = lStrClassName & lStrCamelCasing
    Next

    Return lStrClassName

  End Function


  ''''''''''''''''''''''''''''''''''''''''''''''
  '    Modified by Jar on 2009-Nov
  ''''''''''''''''''''''''''''''''''''''''''''''
  Public Function mFnGnrtDBScript(ByVal aStrTableName As String, ByVal aStrDBStyle As String, ByVal aDgvColmunDgv As DataGridView, ByVal aStrFolderPath As String) As String
    Dim lStrQuery As String = String.Empty
    Dim lCmdData As OracleCommand
    Dim lDrData As OracleDataReader
    Dim lArrScript As New ArrayList
    Dim lObjDataTable As New DataTable
    Dim lObjDataSet As New DataSet
    Dim lObjDataAdapter As OracleDataAdapter

    Dim lIntScriptIndex As Integer
    Dim lIntRefTableId As Integer
    Dim lHstRefTablsIds As New Hashtable

    Dim lStrScriptTemplate As String
    Dim lStrCategory As String
    Dim lStrScript As String
    Dim lStrIndexId As String = String.Empty
    Dim lStrIndexName As String = String.Empty
    Dim lStrClms As String = String.Empty
    Dim lIntArrIndex As Integer
    Dim lArrClms() As String
    Dim lStrFileName As String = "Table_" & aStrTableName & ".sql"

    'Generate CreateTable

    lStrScriptTemplate = ReadTemplateScript(aStrDBStyle, TM_DROP_TABLE)
    lStrScript = lStrScriptTemplate
    lStrScript = ReplaceParameter(lStrScript, TM_TABLE_NAME, aStrTableName)
    lArrScript.Add(lStrScript)

    lStrScriptTemplate = ReadTemplateScript(aStrDBStyle, TM_CREATE_TABLE)
    lStrScript = lStrScriptTemplate
    lStrScript = ReplaceParameter(lStrScript, TM_TABLE_NAME, aStrTableName)

    For Each lObjRow As DataGridViewRow In aDgvColmunDgv.Rows
      lStrCategory = ReadCategory(aStrDBStyle, _
                        lObjRow.Cells("clmFEDataType").Value.ToString.Trim, _
                        lObjRow.Cells("clmBEDBDataType").Value.ToString.Trim)
      If lStrCategory.Contains(TM_LENGTH) Then _
        lStrCategory = lStrCategory.Replace("%" & TM_LENGTH & "%", _
                                            (IIf(IsDBNull(lObjRow.Cells("clmLength").Value), 0, lObjRow.Cells("clmLength").Value) + _
                                             IIf(IsDBNull(lObjRow.Cells("clmPrecision").Value), 0, lObjRow.Cells("clmPrecision").Value)).ToString.Trim)
      If lStrCategory.Contains(TM_PRECISION) Then _
        lStrCategory = lStrCategory.Replace("%" & TM_PRECISION & "%", _
                                            IIf(IsDBNull(lObjRow.Cells("clmPrecision").Value), 0, lObjRow.Cells("clmPrecision").Value).ToString.Trim)
      lStrScript = ReplaceParameter(lStrScript, TM_START, String.Empty)
      lStrScript = ReplaceParameter(lStrScript, TM_COLUMN_NAME, lObjRow.Cells("clmClmName").Value.ToString.Trim)
      lStrScript = ReplaceParameter(lStrScript, TM_CATEGORY, lStrCategory)
      lStrScript = ReplaceParameter(lStrScript, DDL_NULL, IIf(lObjRow.Cells("clmNull").Value = "Y", "NOT NULL", "NULL"))
    Next
    lStrScript = ReplaceParameter(lStrScript, TM_END, String.Empty)
    lArrScript.Add(lStrScript)

    'Generate PK/FK/Index
    mPrRefreshConn()
    lStrQuery = "SELECT * FROM TRDX_TABL_INDX T,TRDX_TABLES_MSTR B,TRDX_TABLE_COLUMNS C" & _
                " WHERE T.TTL_TABL_IDXX=B.TABM_TABLE_ID" & _
                " AND T.TTL_TABL_IDXX=C.TAB_TABLE_ID" & _
                " AND T.TTL_INDX_TYPE='P'" & _
                " AND C.TAB_COL_ID=T.TTL_COLM_IDXX" & _
                " AND B.TABM_TABLE_NAME='" & aStrTableName & "'"
    lCmdData = New OracleCommand(lStrQuery, con)
    lDrData = lCmdData.ExecuteReader

    lStrScriptTemplate = ReadTemplateScript(aStrDBStyle, TM_CREATE_PK)

    If lDrData.HasRows Then
      lStrScript = lStrScriptTemplate
      lStrScript = ReplaceParameter(lStrScript, TM_TABLE_NAME, aStrTableName)
      While lDrData.Read
        lStrScript = ReplaceParameter(lStrScript, TM_START, String.Empty)
        lStrScript = ReplaceParameter(lStrScript, TM_PK_Name, lDrData.Item("TTL_INDX_NAME"))
        lStrScript = ReplaceParameter(lStrScript, TM_COLUMN_NAME, lDrData.Item("TAB_COL_NAME"))
      End While
      lStrScript = ReplaceParameter(lStrScript, TM_END, String.Empty)
      lArrScript.Add(lStrScript)
    End If

    lObjDataTable.Clear()
    lStrQuery = "SELECT * FROM TRDX_FRGN_KEY_DTLS T " & _
                " WHERE T.TFK_TABLE_NAME='" & aStrTableName & "'" & _
                " ORDER BY T.TFK_TABLE_ID,T.TFK_FRGN_KEY_ID,T.TFK_FRGN_KEY_INDX_ID"
    lObjDataAdapter = New OracleDataAdapter(lStrQuery, con)
    lObjDataAdapter.Fill(lObjDataSet)
    lObjDataAdapter.Dispose()
    lObjDataAdapter = Nothing
    lObjDataTable = lObjDataSet.Tables(0)

    lStrScriptTemplate = ReadTemplateScript(aStrDBStyle, TM_CREATE_FK)
    lHstRefTablsIds.Clear()
    For Each lObjRow As DataRow In lObjDataTable.Rows
      lIntRefTableId = lObjRow("TFK_RFRNCE_TABLE_ID")
      'If lHstRefTablsIds.Contains(lIntRefTableId) Then
      '  lIntScriptIndex = lHstRefTablsIds(lIntRefTableId)
      '  lStrScript = lArrScript.Item(lIntScriptIndex)
      'Else
      lStrScript = lStrScriptTemplate
      lIntScriptIndex = lArrScript.Add(lStrScript)
      'lHstRefTablsIds.Add(lIntRefTableId, lIntScriptIndex)
      'End If

      lStrScript = ReplaceParameter(lStrScript, TM_FK_NAME, "FK_" & (New clsDBScriptGnrt).GetTableShrtName(lObjRow("TFK_TABLE_ID")) & "_" & lObjRow("TFK_FRGN_KEY_INDX_ID").ToString & lObjRow("TFK_COLUMN_ID").ToString & lObjRow("TFK_RFRNCE_COLUMN_ID").ToString)
      lStrScript = ReplaceParameter(lStrScript, TM_TABLE_NAME, lObjRow("TFK_TABLE_NAME").ToString)
      lStrScript = ReplaceParameter(lStrScript, TM_REF_TABLE_NAME, lObjRow("TFK_RFRNCE_TABLE_NAME").ToString)
      lStrScript = ReplaceParameter(lStrScript, TM_START, String.Empty)
      lStrScript = ReplaceParameter(lStrScript, TM_COLUMN_NAME, lObjRow("TFK_COLUMN_NAME").ToString)
      lStrScript = ReplaceParameter(lStrScript, TM_REF_COLUMN_NAME, lObjRow("TFK_RFRNCE_COLUMN_NAME").ToString)
      lArrScript.Item(lIntScriptIndex) = lStrScript
    Next

    lObjDataTable.Clear()
    lStrQuery = "SELECT * FROM TRDX_TABL_INDX T,TRDX_TABLES_MSTR B,TRDX_TABLE_COLUMNS C" & _
                 " WHERE T.TTL_TABL_IDXX=B.TABM_TABLE_ID" & _
                 " AND T.TTL_TABL_IDXX=C.TAB_TABLE_ID" & _
                 " AND T.TTL_INDX_TYPE='I'" & _
                 " AND C.TAB_COL_ID=T.TTL_COLM_IDXX" & _
                 " AND B.TABM_TABLE_NAME='" & aStrTableName & "'" & _
                 " ORDER BY T.TTL_INDX_IDXX"
    lObjDataAdapter = New OracleDataAdapter(lStrQuery, con)
    lObjDataAdapter.Fill(lObjDataSet)
    lObjDataAdapter.Dispose()
    lObjDataAdapter = Nothing
    lObjDataTable = lObjDataSet.Tables(0)

    lStrScriptTemplate = ReadTemplateScript(aStrDBStyle, TM_CREATE_INDEX)

    For Each lObjRow As DataRow In lObjDataTable.Rows

      If lObjRow("TTL_INDX_IDXX").ToString = lStrIndexId Then
        lStrClms += " , " & lObjRow("TAB_COL_NAME")
      Else
        lIntArrIndex = lArrScript.Add(String.Empty)
        lStrIndexId = lObjRow("TTL_INDX_IDXX")
        lStrClms = lObjRow("TAB_COL_NAME")
        lStrIndexName = lObjRow("TTL_INDX_NAME")
      End If

      lStrScript = lStrScriptTemplate
      lStrScript = ReplaceParameter(lStrScript, TM_INDEX_NAME, lStrIndexName)
      lStrScript = ReplaceParameter(lStrScript, TM_TABLE_NAME, aStrTableName)
      lArrClms = Split(lStrClms, " , ")
      For Each lStrClm As String In lArrClms
        lStrScript = ReplaceParameter(lStrScript, TM_START, String.Empty)
        lStrScript = ReplaceParameter(lStrScript, TM_COLUMN_NAME, lStrClm)
      Next
      lStrScript = ReplaceParameter(lStrScript, TM_END, String.Empty)
      lStrScript = ReplaceParameter(lStrScript, TM_REMOVE, String.Empty)
      lArrScript(lIntArrIndex) = lStrScript

    Next

    lStrScript = String.Empty
    For Each lStr As String In lArrScript
      lStr = ReplaceParameter(lStr, TM_END, String.Empty)
      lStr = ReplaceParameter(lStr, TM_REMOVE, String.Empty)
      lStr = ReplaceSepcChar(lStr)
      lStrScript = lStrScript + lStr + vbCrLf
    Next

    If WriteFile(aStrFolderPath, lStrFileName, lStrScript) = True Then
      Return aStrFolderPath & "\" & lStrFileName
    Else
      Return String.Empty
    End If

  End Function

  Public Function mFnGnrtCode(ByVal aStrTableName As String, _
                              ByVal aStrTableShrtName As String, _
                              ByVal aStrTableDesc As String, _
                              ByVal aStrDataCategory As String, _
                              ByVal aStrBaseCategory As String, _
                              ByVal aDgvColmunDgv As DataGridView, _
                              ByVal aStrFolderPath As String, _
                              ByVal aStrFileName As String, _
                              ByVal aStrModelName As String, _
                              ByVal aStrSaveClientIndc As String) As String
    Dim lStrTemplate As String = String.Empty
    Dim lStrTemplateFile As String = Application.StartupPath & "\" & CODE_TEMPLATE_FILE

    Select Case aStrSaveClientIndc
      Case CLIENT_INDC_FE
        If mFnReadValue(aStrModelName & "FileTemplate", String.Empty, String.Empty, lStrTemplate, lStrTemplateFile) = False Then Return String.Empty
        lStrTemplate = mFnGnrtFECode(aStrTableName, aStrTableDesc, aDgvColmunDgv, lStrTemplate)

      Case CLIENT_INDC_BE
        If mFnReadValue(aStrModelName & "FileTemplate", String.Empty, String.Empty, lStrTemplate, lStrTemplateFile) = False Then Return String.Empty
        lStrTemplate = mFnGnrtBECode(aStrTableName, aStrTableShrtName, aStrTableDesc, aDgvColmunDgv, lStrTemplate)

      Case CLIENT_INDC_FE & CLIENT_INDC_CATEGORY, CLIENT_INDC_BE & CLIENT_INDC_CATEGORY
        If mFnReadValue(aStrModelName & "FileTemplate", String.Empty, String.Empty, lStrTemplate, lStrTemplateFile) = False Then Return String.Empty
        lStrTemplate = mFnGnrtCategoryCode(aStrDataCategory, aStrBaseCategory, aStrSaveClientIndc, lStrTemplate)

    End Select

    If WriteFile(aStrFolderPath, aStrFileName, lStrTemplate) = True Then
      Return aStrFolderPath & "\" & aStrFileName
    Else
      Return String.Empty
    End If

  End Function

  Private Function mFnGnrtFECode(ByVal aStrTableName As String, ByVal aStrTableDesc As String, ByVal aDgvColmunDgv As DataGridView, ByVal aStrTemplate As String) As String
    Dim lIntLineNum As Integer = 0
    Dim lStrTemplateLines() As String
    Dim lStrTemplate As String = String.Empty
    Dim lStrTableClassTemplateFile As String = Application.StartupPath & "\" & CODE_TEMPLATE_FILE
    Dim lStrDateTime As String = Now.ToString
    Dim lStrYear As String = Now.Year.ToString
    Dim lStrConditionName As String = String.Empty
    Dim lStrPropertyValue As String = String.Empty
    Dim lStrPropertyName As String = String.Empty

    Dim lStrCategoryValue As String = String.Empty
    Dim lArrCategorys As ArrayList
    lArrCategorys = mFnReadElements(TM_FE_DB_CATEGORY, Application.StartupPath + "\" + CODE_TEMPLATE_FILE)

    lStrTemplateLines = Split(aStrTemplate, vbCrLf)

    Try

      lStrTemplate = String.Empty
      For Each lStrLine As String In lStrTemplateLines
        lIntLineNum += 1
        lStrLine = lStrLine.Replace("\n", vbCrLf)
        lStrLine = lStrLine.Replace("\t", Space(TAB_SPACES))

        Select Case ProcessTemplateString(lStrLine)
          Case EnmLineStauts.NONE
            lStrTemplate = lStrTemplate & lStrLine & vbCrLf
            Continue For

          Case EnmLineStauts.LOOP_PARAMETER
            For Each lObjRow As DataGridViewRow In aDgvColmunDgv.Rows
              lStrLine = ReplaceParameter(lStrLine, TM_START, String.Empty)
              lStrLine = ReplaceParameter(lStrLine, TM_NULL_PARAMETER, String.Empty)
              lStrLine = ReplaceParameter(lStrLine, TM_DATETIME, lStrDateTime)
              lStrLine = ReplaceParameter(lStrLine, TM_YEAR, lStrYear)
              lStrLine = ReplaceParameter(lStrLine, TM_TABLE_NAME, aStrTableName)
              lStrLine = ReplaceParameter(lStrLine, TM_TABLE_DESC, aStrTableDesc)
              lStrLine = ReplaceParameter(lStrLine, TM_COLUMN_NAME, lObjRow.Cells("clmClmName").Value.ToString.Trim)
              lStrLine = ReplaceParameter(lStrLine, TM_FE_CATEGORY, lObjRow.Cells("clmFECategory").Value.ToString.Trim)

              For Each lStrCategory As String In lArrCategorys
                mFnReadValue(TM_FE_DB_CATEGORY, lStrCategory, String.Empty, lStrCategoryValue, Application.StartupPath + "\" + CODE_TEMPLATE_FILE)
                If UCase(lObjRow.Cells("clmFEDataType").Value.ToString.Trim) = UCase(lStrCategoryValue) Then
                  lStrLine = ReplaceConditionValue(lStrLine, lStrCategory & TM_CATEGORY, True)
                Else
                  lStrLine = ReplaceConditionValue(lStrLine, lStrCategory & TM_CATEGORY, False)
                End If
              Next

            Next
            lStrLine = ReplaceParameter(lStrLine, TM_END, String.Empty)
            lStrLine = ReplaceParameter(lStrLine, TM_REMOVE, String.Empty)
            lStrTemplate = lStrTemplate & lStrLine & vbCrLf
            Continue For

          Case EnmLineStauts.CONDITION
            lStrConditionName = GetConditionPropertyName(lStrLine)
            lStrPropertyValue = GetPropertyValue(lStrConditionName, lStrTableClassTemplateFile)
            lStrPropertyName = GetPropertyName(lStrConditionName, lStrTableClassTemplateFile)

            If lStrPropertyName = PRP_COLUMN Then
              Dim lBln As Boolean
              Dim lStrClmName As String = GetCondition(lStrPropertyValue)
              Dim lStrTrueValue As String = GetConditionValue(lStrPropertyValue, True)
              Dim lStrFalseValue As String = GetConditionValue(lStrPropertyValue, False)
              For Each lObjRow As DataGridViewRow In aDgvColmunDgv.Rows
                If lObjRow.Cells(lStrClmName).Value = lStrTrueValue Then
                  lBln = True
                  lStrLine = ReplaceParameter(lStrLine, TM_START, String.Empty)
                  lStrLine = ReplaceParameter(lStrLine, TM_NULL_PARAMETER, String.Empty)
                  lStrLine = ReplaceParameter(lStrLine, TM_PK_NAME, lObjRow.Cells("clmClmName").Value.ToString.Trim)
                  lStrLine = ReplaceParameter(lStrLine, TM_DATETIME, lStrDateTime)
                  lStrLine = ReplaceParameter(lStrLine, TM_YEAR, lStrYear)
                  lStrLine = ReplaceParameter(lStrLine, TM_TABLE_DESC, aStrTableDesc)
                  lStrLine = ReplaceParameter(lStrLine, TM_TABLE_NAME, aStrTableName)
                  lStrLine = ReplaceParameter(lStrLine, TM_FE_CATEGORY, lObjRow.Cells("clmFECategory").Value.ToString.Trim)

                  For Each lStrCategory As String In lArrCategorys
                    mFnReadValue(TM_FE_DB_CATEGORY, lStrCategory, String.Empty, lStrCategoryValue, Application.StartupPath + "\" + CODE_TEMPLATE_FILE)
                    If UCase(lObjRow.Cells("clmFEDataType").Value.ToString.Trim) = UCase(lStrCategoryValue) Then
                      lStrLine = ReplaceConditionValue(lStrLine, lStrCategory & TM_CATEGORY, True)
                    Else
                      lStrLine = ReplaceConditionValue(lStrLine, lStrCategory & TM_CATEGORY, False)
                    End If
                  Next

                End If
              Next
              lStrLine = ReplaceParameter(lStrLine, TM_END, String.Empty)
              lStrLine = ReplaceParameter(lStrLine, TM_REMOVE, String.Empty)
              If lBln = True Then _
                lStrTemplate = lStrTemplate & lStrLine & vbCrLf
              Continue For
            End If

          Case EnmLineStauts.SINGLE_PARAMETER
            lStrLine = ReplaceParameter(lStrLine, TM_DATETIME, lStrDateTime)
            lStrLine = ReplaceParameter(lStrLine, TM_YEAR, lStrYear)
            lStrLine = ReplaceParameter(lStrLine, TM_TABLE_NAME, aStrTableName)
            lStrLine = ReplaceParameter(lStrLine, TM_TABLE_DESC, aStrTableDesc)
            lStrTemplate = lStrTemplate & lStrLine & vbCrLf
            Continue For

        End Select

      Next
      lStrTemplate = ReplaceSepcChar(lStrTemplate)

    Catch ex As Exception
      MsgBox("Template Error in Line Number : " & lIntLineNum.ToString, MsgBoxStyle.Information, gCONST_MSG_HEADER)
      Return lStrTemplate
    End Try

    Return lStrTemplate
  End Function

  Private Function mFnGnrtBECode(ByVal aStrTableName As String, ByVal aStrTableShrtName As String, ByVal aStrTableDesc As String, ByVal aDgvColmunDgv As DataGridView, ByVal aStrTemplate As String) As String
    Dim lIntLineNum As Integer = 0
    Dim lStrTemplateLines() As String
    Dim lStrTemplate As String = String.Empty
    Dim lStrTableClassTemplateFile As String = Application.StartupPath & "\" & CODE_TEMPLATE_FILE

    Dim lIntNumber As Integer = 0
    Dim lStrDateTime As String = Now.ToString
    Dim lStrYear As String = Now.Year.ToString
    Dim lStrConditionName As String = String.Empty
    Dim lStrPropertyValue As String = String.Empty
    Dim lStrPropertyName As String = String.Empty
    Dim lStrVaiableName As String = String.Empty
    Dim lStrProCTableShrtName As String = StrConv(aStrTableName.Replace("TRDX_", String.Empty).Replace("_", " "), VbStrConv.ProperCase).Replace(" ", String.Empty)
    Dim lStrStructName As String = Replace(aStrTableName, TABLE_HEAD, String.Empty)

    Dim lStrCategoryValue As String = String.Empty
    Dim lArrCategorys As ArrayList
    lArrCategorys = mFnReadElements(TM_BE_CATEGORY, Application.StartupPath + "\" + CODE_TEMPLATE_FILE)

    lStrTemplateLines = Split(aStrTemplate, vbCrLf)

    Try

      lStrTemplate = String.Empty
      For Each lStrLine As String In lStrTemplateLines
        lIntLineNum += 1
        lStrLine = lStrLine.Replace("\n", vbCrLf)
        lStrLine = lStrLine.Replace("\t", Space(TAB_SPACES))

        Select Case ProcessTemplateString(lStrLine)
          Case EnmLineStauts.NONE
            lStrTemplate = lStrTemplate & lStrLine & vbCrLf
            Continue For

          Case EnmLineStauts.LOOP_PARAMETER
            lIntNumber = 0
            For Each lObjRow As DataGridViewRow In aDgvColmunDgv.Rows
              lIntNumber += 1
              lStrVaiableName = StrConv(lObjRow.Cells("clmClmName").Value.ToString.Trim.Replace("_", " "), VbStrConv.ProperCase).Replace(" ", String.Empty)
              lStrLine = ReplaceParameter(lStrLine, TM_START, String.Empty)
              lStrLine = ReplaceParameter(lStrLine, TM_NULL_PARAMETER, String.Empty)
              lStrLine = ReplaceParameter(lStrLine, TM_TABLE_NAME, aStrTableName)
              lStrLine = ReplaceParameter(lStrLine, TM_COLUMN_NAME, lObjRow.Cells("clmClmName").Value.ToString.Trim)
              lStrLine = ReplaceParameter(lStrLine, TM_DATA_TYPE, lObjRow.Cells("clmClmTypeValueMember").Value.ToString.Trim)
              lStrLine = ReplaceParameter(lStrLine, TM_VARIABLE_NAME, lStrVaiableName)
              lStrLine = ReplaceParameter(lStrLine, TM_TABLE_VARIAVLE_NAME, lStrProCTableShrtName)
              lStrLine = ReplaceParameter(lStrLine, TM_NUMBER, lIntNumber)
              lStrLine = ReplaceParameter(lStrLine, TM_DATETIME, lStrDateTime)
              lStrLine = ReplaceParameter(lStrLine, TM_YEAR, lStrYear)
              lStrLine = ReplaceParameter(lStrLine, TM_STRUCT_NAME, lStrStructName)
              lStrLine = ReplaceParameter(lStrLine, TM_TABLE_DESC, aStrTableDesc)
              lStrLine = ReplaceParameter(lStrLine, TM_TABLE_SHRT_NAME, aStrTableShrtName)

              For Each lStrCategory As String In lArrCategorys
                mFnReadValue(TM_BE_CATEGORY, lStrCategory, String.Empty, lStrCategoryValue, Application.StartupPath + "\" + CODE_TEMPLATE_FILE)
                If lStrCategory = BE_DATE_CATEGORY Then
                  If UCase(lObjRow.Cells("clmBEDataType").Value.ToString.Trim) = UCase(lStrCategoryValue) _
                  Or UCase(lObjRow.Cells("clmClmTypeValueMember").Value.ToString.Trim) = UCase(lStrCategoryValue) Then
                    lStrLine = ReplaceConditionValue(lStrLine, lStrCategory & TM_CATEGORY, True)
                  Else
                    lStrLine = ReplaceConditionValue(lStrLine, lStrCategory & TM_CATEGORY, False)
                  End If
                Else
                  If UCase(lObjRow.Cells("clmBEDataType").Value.ToString.Trim) = UCase(lStrCategoryValue) Then
                    lStrLine = ReplaceConditionValue(lStrLine, lStrCategory & TM_CATEGORY, True)
                  Else
                    lStrLine = ReplaceConditionValue(lStrLine, lStrCategory & TM_CATEGORY, False)
                  End If
                End If
              Next

            Next
            lStrLine = ReplaceParameter(lStrLine, TM_END, String.Empty)
            lStrLine = ReplaceParameter(lStrLine, TM_REMOVE, String.Empty)
            lStrTemplate = lStrTemplate & lStrLine & vbCrLf
            Continue For

          Case EnmLineStauts.CONDITION
            lIntNumber = 0
            lStrConditionName = GetConditionPropertyName(lStrLine)
            lStrPropertyValue = GetPropertyValue(lStrConditionName, lStrTableClassTemplateFile)
            lStrPropertyName = GetPropertyName(lStrConditionName, lStrTableClassTemplateFile)

            If lStrPropertyName = PRP_COLUMN Then
              Dim lBln As Boolean
              Dim lStrClmName As String = GetCondition(lStrPropertyValue)
              Dim lStrTrueValue As String = GetConditionValue(lStrPropertyValue, True)
              Dim lStrFalseValue As String = GetConditionValue(lStrPropertyValue, False)
              For Each lObjRow As DataGridViewRow In aDgvColmunDgv.Rows
                lIntNumber += 1
                If lObjRow.Cells(lStrClmName).Value = lStrTrueValue Then
                  lBln = True
                  lStrVaiableName = StrConv(lObjRow.Cells("clmClmName").Value.ToString.Trim.Replace("_", " "), VbStrConv.ProperCase).Replace(" ", String.Empty)
                  lStrLine = ReplaceParameter(lStrLine, TM_START, String.Empty)
                  lStrLine = ReplaceParameter(lStrLine, TM_NULL_PARAMETER, String.Empty)
                  lStrLine = ReplaceParameter(lStrLine, TM_TABLE_NAME, aStrTableName)
                  lStrLine = ReplaceParameter(lStrLine, TM_PK_NAME, lObjRow.Cells("clmClmName").Value.ToString.Trim)
                  lStrLine = ReplaceParameter(lStrLine, TM_VARIABLE_NAME, lStrVaiableName)
                  lStrLine = ReplaceParameter(lStrLine, TM_TABLE_VARIAVLE_NAME, lStrProCTableShrtName)
                  lStrLine = ReplaceParameter(lStrLine, TM_DATA_TYPE, lObjRow.Cells("clmClmTypeValueMember").Value.ToString.Trim)
                  lStrLine = ReplaceParameter(lStrLine, TM_NUMBER, lIntNumber)
                  lStrLine = ReplaceParameter(lStrLine, TM_DATETIME, lStrDateTime)
                  lStrLine = ReplaceParameter(lStrLine, TM_YEAR, lStrYear)
                  lStrLine = ReplaceParameter(lStrLine, TM_STRUCT_NAME, lStrStructName)
                  lStrLine = ReplaceParameter(lStrLine, TM_TABLE_DESC, aStrTableDesc)
                  lStrLine = ReplaceParameter(lStrLine, TM_TABLE_SHRT_NAME, aStrTableShrtName)

                  For Each lStrCategory As String In lArrCategorys
                    mFnReadValue(TM_BE_CATEGORY, lStrCategory, String.Empty, lStrCategoryValue, Application.StartupPath + "\" + CODE_TEMPLATE_FILE)
                    If lStrCategory = BE_DATE_CATEGORY Then
                      If UCase(lObjRow.Cells("clmBEDataType").Value.ToString.Trim) = UCase(lStrCategoryValue) _
                      Or UCase(lObjRow.Cells("clmClmTypeValueMember").Value.ToString.Trim) = UCase(lStrCategoryValue) Then
                        lStrLine = ReplaceConditionValue(lStrLine, lStrCategory & TM_CATEGORY, True)
                      Else
                        lStrLine = ReplaceConditionValue(lStrLine, lStrCategory & TM_CATEGORY, False)
                      End If
                    Else
                      If UCase(lObjRow.Cells("clmBEDataType").Value.ToString.Trim) = UCase(lStrCategoryValue) Then
                        lStrLine = ReplaceConditionValue(lStrLine, lStrCategory & TM_CATEGORY, True)
                      Else
                        lStrLine = ReplaceConditionValue(lStrLine, lStrCategory & TM_CATEGORY, False)
                      End If
                    End If
                  Next

                End If
              Next
              lStrLine = ReplaceParameter(lStrLine, TM_END, String.Empty)
              lStrLine = ReplaceParameter(lStrLine, TM_REMOVE, String.Empty)
              If lBln = True Then _
                lStrTemplate = lStrTemplate & lStrLine & vbCrLf
              Continue For
            End If

          Case EnmLineStauts.SINGLE_PARAMETER
            lStrLine = ReplaceParameter(lStrLine, TM_DATETIME, lStrDateTime)
            lStrLine = ReplaceParameter(lStrLine, TM_YEAR, lStrYear)
            lStrLine = ReplaceParameter(lStrLine, TM_TABLE_NAME, aStrTableName)
            lStrLine = ReplaceParameter(lStrLine, TM_STRUCT_NAME, lStrStructName)
            lStrLine = ReplaceParameter(lStrLine, TM_TABLE_DESC, aStrTableDesc)
            lStrLine = ReplaceParameter(lStrLine, TM_TABLE_VARIAVLE_NAME, lStrProCTableShrtName)
            lStrLine = ReplaceParameter(lStrLine, TM_TABLE_SHRT_NAME, aStrTableShrtName)
            lStrTemplate = lStrTemplate & lStrLine & vbCrLf
            Continue For

        End Select

      Next
      lStrTemplate = ReplaceSepcChar(lStrTemplate)

    Catch ex As Exception
      MsgBox("Template Error in Line Number : " & lIntLineNum.ToString, MsgBoxStyle.Information, gCONST_MSG_HEADER)
      Return lStrTemplate
    End Try

    Return lStrTemplate
  End Function

  Private Function mFnGnrtCategoryCode(ByVal aStrDateCategory As String, _
                                       ByVal aStrBaseCategory As String, _
                                       ByVal aStrCodeClientIndc As String, _
                                       ByVal aStrTemplate As String) As String
    Dim lIntLineNum As Integer = 0
    Dim lStrQuery As String = String.Empty
    Dim lCmdData As OracleCommand
    Dim lDrData As OracleDataReader
    Dim lStrTemplateLines() As String
    Dim lStrTemplate As String = String.Empty
    Dim lStrTableClassTemplateFile As String = Application.StartupPath & "\" & CODE_TEMPLATE_FILE
    Dim lStrDateTime As String = Now.ToString
    Dim lStrYear As String = Now.Year.ToString
    Dim lStrCategoryConditionName As String = String.Empty
    Dim lStrCategoryDesc As String = String.Empty

    Dim lStrCategoryValue As String = String.Empty
    Dim lArrCategorys As ArrayList
    lArrCategorys = mFnReadElements(TM_FE_DB_CATEGORY, Application.StartupPath + "\" + CODE_TEMPLATE_FILE)


    mPrRefreshConn()
    lStrQuery = "SELECT * FROM TRDX_DATA_CATEGORY_MSTR T WHERE T.TDCM_FRNT_END_DATA_CATEGORY='" & aStrDateCategory & "'"
    lCmdData = New OracleCommand(lStrQuery, con)
    lDrData = lCmdData.ExecuteReader

    If lDrData.HasRows Then
      While lDrData.Read
        If aStrCodeClientIndc = CLIENT_INDC_FE & CLIENT_INDC_CATEGORY Then
          lStrCategoryConditionName = lDrData.Item("TDCM_FRNT_END_DATA_TYPE")
        ElseIf aStrCodeClientIndc = CLIENT_INDC_BE & CLIENT_INDC_CATEGORY Then
          lStrCategoryConditionName = lDrData.Item("TDCM_DATABASE_DATA_TYPE")
        End If
        lStrCategoryDesc = lDrData.Item("TDCM_DESC")
      End While
    End If

    lStrTemplateLines = Split(aStrTemplate, vbCrLf)

    Try

      lStrTemplate = String.Empty
      For Each lStrLine As String In lStrTemplateLines
        lIntLineNum += 1
        lStrLine = lStrLine.Replace("\n", vbCrLf)
        lStrLine = lStrLine.Replace("\t", Space(TAB_SPACES))

        Select Case ProcessTemplateString(lStrLine)
          Case EnmLineStauts.NONE
            lStrTemplate = lStrTemplate & lStrLine & vbCrLf
            Continue For

          Case EnmLineStauts.LOOP_PARAMETER
            lStrLine = ReplaceParameter(lStrLine, TM_START, String.Empty)
            lStrLine = ReplaceParameter(lStrLine, TM_NULL_PARAMETER, String.Empty)
            lStrLine = ReplaceParameter(lStrLine, TM_DATETIME, lStrDateTime)
            lStrLine = ReplaceParameter(lStrLine, TM_YEAR, lStrYear)
            lStrLine = ReplaceParameter(lStrLine, TM_CATEGORY, aStrDateCategory)
            lStrLine = ReplaceParameter(lStrLine, TM_BASE_CATEGORY, aStrBaseCategory)
            lStrLine = ReplaceParameter(lStrLine, TM_CATEGORY_DESC, lStrCategoryDesc)

            For Each lStrCategory As String In lArrCategorys
              If aStrCodeClientIndc = CLIENT_INDC_FE & CLIENT_INDC_CATEGORY Then
                mFnReadValue(TM_FE_DB_CATEGORY, lStrCategory, String.Empty, lStrCategoryValue, Application.StartupPath + "\" + CODE_TEMPLATE_FILE)
              ElseIf aStrCodeClientIndc = CLIENT_INDC_BE & CLIENT_INDC_CATEGORY Then
                mFnReadValue(TM_BE_DB_CATEGORY, lStrCategory, String.Empty, lStrCategoryValue, Application.StartupPath + "\" + CODE_TEMPLATE_FILE)
              End If
              If UCase(lStrCategoryConditionName) = UCase(lStrCategoryValue) Then
                lStrLine = ReplaceConditionValue(lStrLine, lStrCategory & TM_CATEGORY, True)
              Else
                lStrLine = ReplaceConditionValue(lStrLine, lStrCategory & TM_CATEGORY, False)
              End If
            Next
            lStrLine = ReplaceParameter(lStrLine, TM_END, String.Empty)
            lStrLine = ReplaceParameter(lStrLine, TM_REMOVE, String.Empty)
            lStrTemplate = lStrTemplate & lStrLine & vbCrLf
            Continue For

          Case EnmLineStauts.SINGLE_PARAMETER
            lStrLine = ReplaceParameter(lStrLine, TM_DATETIME, lStrDateTime)
            lStrLine = ReplaceParameter(lStrLine, TM_YEAR, lStrYear)
            lStrLine = ReplaceParameter(lStrLine, TM_CATEGORY, aStrDateCategory)
            lStrLine = ReplaceParameter(lStrLine, TM_BASE_CATEGORY, aStrBaseCategory)
            lStrLine = ReplaceParameter(lStrLine, TM_CATEGORY_DESC, lStrCategoryDesc)

            For Each lStrCategory As String In lArrCategorys
              If aStrCodeClientIndc = CLIENT_INDC_FE & CLIENT_INDC_CATEGORY Then
                mFnReadValue(TM_FE_DB_CATEGORY, lStrCategory, String.Empty, lStrCategoryValue, Application.StartupPath + "\" + CODE_TEMPLATE_FILE)
              ElseIf aStrCodeClientIndc = CLIENT_INDC_BE & CLIENT_INDC_CATEGORY Then
                mFnReadValue(TM_BE_DB_CATEGORY, lStrCategory, String.Empty, lStrCategoryValue, Application.StartupPath + "\" + CODE_TEMPLATE_FILE)
              End If
              If UCase(lStrCategoryConditionName) = UCase(lStrCategoryValue) Then
                lStrLine = ReplaceConditionValue(lStrLine, lStrCategory & TM_CATEGORY, True)
              Else
                lStrLine = ReplaceConditionValue(lStrLine, lStrCategory & TM_CATEGORY, False)
              End If
            Next

            lStrTemplate = lStrTemplate & lStrLine & vbCrLf
            Continue For

        End Select
      Next
      lStrTemplate = ReplaceSepcChar(lStrTemplate)

    Catch ex As Exception
      MsgBox("Template Error in Line Number : " & lIntLineNum.ToString, MsgBoxStyle.Information, gCONST_MSG_HEADER)
      Return lStrTemplate
    End Try

    Return lStrTemplate
  End Function

End Class

